

/**********************************************************************
Add Disease
***********************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'AddDisease') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure AddDisease
GO

create procedure AddDisease
	@name nvarchar(50),
	@pinyin nvarchar(50),
	@diseasetypeid int,
	@introduction nvarchar(4000),
	@description nvarchar(max),
	@uploaddate datetime,
	@classPatientId int,
	@PatientCount int,
	@id int OUTPUT
as

SET NOCOUNT ON

INSERT INTO [disease]
(
	diseasename,
	firstsymbolofname,
	diseasetypeid,
	Introduction,
	description,      
	uploaddate,
	classPatientId,
	PatientCount
)values(
	@name,
	@pinyin,
	@diseasetypeid,
	@introduction,
	@description,
	@uploaddate,
	@classPatientId,
	@PatientCount
)

SET @id = SCOPE_IDENTITY()
   
GO

/**********************************************************************
UpdateDisease
***********************************************************************/
if exists 
	(
		select * from dbo.sysobjects 
		where id = object_id(N'UpdateDisease') 
		and OBJECTPROPERTY(id, N'IsProcedure') = 1
	)
drop procedure UpdateDisease

GO

create procedure UpdateDisease
	@id int,
	@name nvarchar(50),
	@pinyin nvarchar(50),
	@type int,
	@intro nvarchar(4000),
	@desp nvarchar(max),
	@updatetime datetime,
	@classPatientId int,
    @ClassPatientName nvarchar(50)
as
	UPDATE [disease]
	SET [diseasename] = @name
		,[Introduction] = @intro
		,[description] = @desp
		,[diseasetypeid] = @type
		,[firstsymbolofname] = @pinyin
		,[uploaddate] = @updatetime
		,[classPatientId] = @classPatientId
        ,[ClassPatientName] = @ClassPatientName
	WHERE [id]=@id
   
GO

/**********************************************************************
UpdateDiseasePatientCount
***********************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'UpdateDiseasePatientCount') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure UpdateDiseasePatientCount

GO

create procedure UpdateDiseasePatientCount
	@id int,
	@Num int
as
	UPDATE 
		[disease]
	SET 
		[PatientCount] = [PatientCount] + @Num
	WHERE 
		[id]=@id
   
GO


--region [dbo].[GetDiseaseIdByName]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetDiseaseIdByName]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetDiseaseIdByName]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetDiseaseIdByName]
GO

CREATE PROCEDURE [dbo].[GetDiseaseIdByName]
	@DiseaseName nvarchar(512)
AS

SELECT 
	[id]
FROM
	[Disease]
WHERE
	[Disease].[DiseaseName] = @DiseaseName

GO
--endregion


/**********************************************************************
  GetDisease
**********************************************************************/	 
	 
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetDisease') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetDisease
GO

create procedure GetDisease
   @id int
as
   select 
        'Id' = [disease].[id]
        ,'Name' = [disease].[diseasename]
        ,'Pinyin' = [disease].[firstsymbolofname]
        ,'Intro' = [disease].[Introduction]
        ,'Desp' = [disease].[description]
        ,'UpdateTime' = [disease].[uploaddate]
        ,'Type' = [disease].[diseasetypeid]
        ,'TypeName' = [diseasetype].[diseasetypename]
		,'PatientId' = [disease].[ClassPatientId]
        ,'PatientName' = [disease].[ClassPatientName]
		,[PatientCount]
   from 
        [disease] left join [diseasetype]
   on 
        [disease].[diseasetypeid] =  [diseasetype].[id]
   where 
        [disease].[id]=@id
   
GO

/**********************************************************************
  DelDisease
**********************************************************************/	 
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'DelDisease') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure DelDisease

GO

create procedure DelDisease
  @condstr nvarchar(512)
as
   DECLARE @strSQL VARCHAR(600)
   set @strSQL = ' delete from [disease] where ' + @condstr
   exec(@strSQL)
GO


--region [dbo].[GetDiseaseListWithPatientInfo]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetDiseaseListWithPatientInfo]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetDiseaseListWithPatientInfo]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetDiseaseListWithPatientInfo]
GO

CREATE PROCEDURE [dbo].[GetDiseaseListWithPatientInfo]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT([disease].id) 
FROM
	[disease], [UserInfo]
WHERE
	[disease].[ClassPatientId] = [UserInfo].[Id] 
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT *
FROM
	( SELECT 
	[disease].[id],
	''Name'' = [diseasename],
	''Intro'' = [Introduction],
	''Type'' = [diseasetypeid],
	''Pinyin'' = [firstsymbolofname],
	''UpdateTime'' = [uploaddate],
	''PatientId'' = [ClassPatientId],
	''PatientName'' = [ClassPatientName],
	''PatientAvatar'' = [UserInfo].[Avatar],
	[PatientCount],
	ROW_NUMBER() OVER ( ORDER BY ' + @OrderByExpression + ' ) AS SerialNumber
FROM
	[disease], [UserInfo]
WHERE
	[disease].[ClassPatientId] = [UserInfo].[Id] 
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' AND ' + @WhereCondition
END

SET @SQL = @SQL + ') AS tblTmp  
	WHERE tblTmp.SerialNumber > '
	+ STR((@PageIndex-1)*@PageSize) + ' AND 
	tblTmp.SerialNumber <= '
	+ STR(@PageIndex*@PageSize)

EXEC sp_executesql @SQL

GO
--endregion

--region [dbo].[GetdiseaseList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetdiseaseList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetdiseaseList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetdiseaseList]
GO

CREATE PROCEDURE [dbo].[GetdiseaseList]
	@PageSize int,
	@PageIndex int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT 
	COUNT(id) 
FROM [disease]
	'
IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END 

EXEC sp_executesql @SQL

SET @SQL = '
SELECT *,
	''PatientAvatar'' = dbo.GetUserAvatar(tblTmp.PatientId)
FROM
	( SELECT 
	[Id],
	''Name'' = [diseasename],
	''Intro'' = [Introduction],
	''Type'' = [diseasetypeid],
	''Pinyin'' = [firstsymbolofname],
	''UpdateTime'' = [uploaddate],
	''PatientId'' = [ClassPatientId],
	''PatientName'' = [ClassPatientName],
	[PatientCount],
	ROW_NUMBER() OVER ( ORDER BY ' + @OrderByExpression + ' ) AS SerialNumber
FROM
	[disease]
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END

SET @SQL = @SQL + ') AS tblTmp  
	WHERE tblTmp.SerialNumber > '
	+ STR((@PageIndex-1)*@PageSize) + ' AND 
	tblTmp.SerialNumber <= '
	+ STR(@PageIndex*@PageSize)

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetTopDiseases]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetTopDiseases]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetTopDiseases]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetTopDiseases]
GO

CREATE PROCEDURE [dbo].[GetTopDiseases]
	@N int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT TOP ' + STR(@N) + '
	[id],
	''Name'' = [diseasename],
	''Intro'' = [Introduction],
	''Type'' = [diseasetypeid],
	''Pinyin'' = [firstsymbolofname],
	''UpdateTime'' = [uploaddate],
	''PatientId'' = [ClassPatientId],
	''PatientName'' = [ClassPatientName],
	[PatientCount]
FROM
	[Disease]
	'

IF @WhereCondition <> ''
BEGIN
	SET @SQL = @SQL + ' WHERE ' + @WhereCondition
END
	
IF @OrderByExpression <> ''
BEGIN
	SET @SQL = @SQL + ' ORDER BY ' + @OrderByExpression 
END	

EXEC sp_executesql @SQL

GO
--endregion


/***********************************************************************
************************************************************************
DiseaseType
************************************************************************
***********************************************************************/

/**********************************************************************
AddDiseaseType
***********************************************************************/  
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'AddDiseaseType') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure AddDiseaseType
GO

create procedure AddDiseaseType
   @name nvarchar(50)
   ,@parentid  int,
   @id int OUTPUT
as
    insert into [diseasetype](
        [parentid]
        ,[diseasetypename]
    )values(
        @parentid,
        @name
    )

SET @id = SCOPE_IDENTITY()   

Go

/**********************************************************************
  GetDiseaseType
**********************************************************************/	

if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetDiseaseType') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetDiseaseType
GO

create procedure GetDiseaseType
   @id int
as
   select 
        'Id' = [diseasetype].[id]
        ,'Name' = [diseasetype].[diseasetypename]
   from 
        [diseasetype]
   where 
        [diseasetype].[id]=@id
GO

/**********************************************************************
DelDiseaseType
***********************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'DelDiseaseType') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure DelDiseaseType
GO


create procedure [DelDiseaseType]
   @condstr nvarchar(512)
as
   DECLARE @strSQL VARCHAR(600)

   set @strSQL = '
   delete from [diseasetype]
   where ' + @condstr

   exec(@strSQL)

GO


/**********************************************************************
UpdateDiseaseType
***********************************************************************/  
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'UpdateDiseaseType') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure UpdateDiseaseType
GO


create procedure UpdateDiseaseType
    @id  int
   ,@name nvarchar(50)
   ,@parentId int
as
   update [diseasetype] set
      [diseasetypename] = @name
     ,[parentid] = @parentid
	where
      [id] = @id
GO



/**********************************************************************
GetDiseaseTypeList
***********************************************************************/  
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetDiseaseTypeList') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetDiseaseTypeList
GO


create procedure [GetDiseaseTypeList]
   @condstr  nvarchar(512)
as
   DECLARE @strSQL VARCHAR(600)
   set @strSQL = 'select 
           ''Id'' = [id]
          ,''Name'' = [diseasetypename]
          ,''ParentId'' = [parentid]
   from
		  [diseasetype]'
	
	 if @condstr <> ''
		 begin
		 	set @strSQL = @strSQL + ' where ' + @condstr
		 end
   
   exec(@strSQL)
GO


/*************************************************************************
AddDiseaseSymptom
*************************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'AddDiseaseSymptom') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure AddDiseaseSymptom
GO


create procedure [AddDiseaseSymptom]
   @diseaseid  int
   ,@symptomid int
   ,@type int
as
   if not exists 
   (
       select top 1 [id] from [diseasesymptom] where [diseaseid]=@diseaseid and [symptomid]=@symptomid
   )
   begin
       insert into [diseasesymptom] (
   		    [diseaseid]
   	     ,[symptomid]	
   	     ,[type])
   	    values(
   	      @diseaseid
   	      ,@symptomid
   	      ,@type
       )
   end
GO

/**********************************************************************
  DelDiseaseSymptoms
**********************************************************************/	 
	 
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'DelDiseaseSymptoms') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure DelDiseaseSymptoms
GO

create procedure DelDiseaseSymptoms
  @condstr nvarchar(512)
as
   DECLARE @strSQL VARCHAR(600)
   set @strSQL = 'delete from [diseasesymptom] where ' + @condstr
   exec(@strSQL)
GO

/**********************************************************************
  GetSelectedSymptomCollect
**********************************************************************/	
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetSelectedSymptomCollect') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetSelectedSymptomCollect
GO

create procedure GetSelectedSymptomCollect
   @diseaseid  int
   ,@type int
as
	select 
        'Id' = [id],
        'Name' = [symptomname]
	from
		[symptom]
	where
		[symptom].[id] not in 
	(
		select 
			[symptomid]
		from 
			[diseasesymptom]
		where
			[diseasesymptom].[diseaseid] = @diseaseid and
			[diseasesymptom].[type] = @type
	)
GO

/**********************************************************************
  GetDiseaseSymptomList
**********************************************************************/	

if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetDiseaseSymptomList') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetDiseaseSymptomList
GO

create procedure GetDiseaseSymptomList
  @diseaseid int
as
   SELECT 
         'SymptomId' = [symptom].[id]
         ,'SymptomName' = [symptom].[symptomname]
         ,'Type' = [diseasesymptom].[type]
   from 
         [diseasesymptom], [symptom] 
   where   
         [diseasesymptom].[symptomid] = [symptom].[id] and
         [diseasesymptom].[diseaseid] = @diseaseid
         
GO

/**********************************************************************
  GetDiseaseSymptomListBYTYPE
**********************************************************************/	

if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetDiseaseSymptomListByType') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetDiseaseSymptomListByType
GO

create procedure GetDiseaseSymptomListByType
  @diseaseid int,
  @type      int
as
   SELECT 
         'SymptomId' = [symptom].[id]
         ,'SymptomName' = [symptom].[symptomname]
         ,'MildDescription' = [symptom].[milddescription]
	     ,'ModerateDescription' = [symptom].[moderatedescription]
	     ,'SevereDescription' = [symptom].[severedescription]
         ,'Type' = [diseasesymptom].[type]
   from 
         [diseasesymptom], [symptom] 
   where   
         [diseasesymptom].[symptomid] = [symptom].[id] and
         [diseasesymptom].[diseaseid] = @diseaseid     and
         [diseasesymptom].[type] = @type
         
GO

/*************************************************************************
AddDiseaseTreatment
*************************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'AddDiseaseTreatment') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure AddDiseaseTreatment
GO


create procedure [AddDiseaseTreatment]
   @diseaseid  int
   ,@treatmentid int
   ,@type int
as
   
   if not exists 
   (
       select top 1 [id] from [diseasetreatment] where [diseaseid]=@diseaseid and [treatmentid]=@treatmentid
   )
   begin
       insert into [diseasetreatment] (
 		  [diseaseid]
   	     ,[treatmentid]	
   	     ,[type])
   	    values(
   	      @diseaseid
   	      ,@treatmentid
   	      ,@type
        )
    end
GO

/**********************************************************************
  DelDiseaseTreatments
**********************************************************************/	 
	 
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'DelDiseaseTreatments') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure DelDiseaseTreatments
GO

create procedure DelDiseaseTreatments
  @condstr nvarchar(512)
as
   DECLARE @strSQL VARCHAR(600)
   set @strSQL = 'delete from [diseasetreatment] where ' + @condstr
   exec(@strSQL)
GO

/**********************************************************************
  GetDiseaseTreatmentList
**********************************************************************/	

if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetDiseaseTreatmentList') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetDiseaseTreatmentList
GO

create procedure GetDiseaseTreatmentList
  @diseaseid int
as
   SELECT 
         'Id' = [diseasetreatment].[id]
         ,'TreatmentId' = [treatment].[id]
         ,'TreatmentName' = [treatment].[treatmentname]
         ,'TreatmentIntro' = [treatment].[introduction]
         ,'TreatmentType' = [treatment].[treatmenttypeid]
   from 
         [diseasetreatment] left join [treatment] 
   on   
         [diseasetreatment].[treatmentid] = [treatment].[id]
   where
         [diseasetreatment].[diseaseid] = @diseaseid
         
GO



/************************************************************************
*************************************************************************
*************************************************************************
*************************************************************************
DiseaseLabcheck
*************************************************************************
*************************************************************************
*************************************************************************
************************************************************************/

/*************************************************************************
AddDiseaseLabcheck
*************************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'AddDiseaseLabcheck') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure AddDiseaseLabcheck
GO


create procedure [AddDiseaseLabcheck]
	@diseaseid  int
	,@labcheckid int
	,@type int
	,@ChartIndex int
	,@AxisIndex int
as
   
   if not exists 
   (
       select top 1 [id] from [diseaselabcheck] where [diseaseid]=@diseaseid and [labcheckid]=@labcheckid
   )
   begin
       insert into [diseaselabcheck] (
 		  [diseaseid]
   	     ,[labcheckid]	
   	     ,[type]
		 ,[ChartIndex]
		 ,[AxisIndex]
		 )
   	    values(
   	      @diseaseid
   	      ,@labcheckid
   	      ,@type
		  ,@ChartIndex
		  ,@AxisIndex
        )
    end
GO


/**********************************************************************
  DelDiseaseLabchecks
**********************************************************************/	 
	 
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'DelDiseaseLabchecks') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure DelDiseaseLabchecks
GO

create procedure DelDiseaseLabchecks
  @condstr nvarchar(512)
as
   DECLARE @strSQL VARCHAR(600)
   set @strSQL = 'delete from [diseaselabcheck] where ' + @condstr
   exec(@strSQL)
GO


/**********************************************************************
  GetDiseaseLabcheckList
**********************************************************************/	

if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetDiseaseLabcheckList') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetDiseaseLabcheckList
GO

create procedure GetDiseaseLabcheckList
  @diseaseid int
as
   SELECT 
         'Id' = [diseaselabcheck].[id]
         ,'LabcheckId' = [labcheck].[id]
         ,'LabcheckName' = [labcheck].[indicatorname]
         ,'LabcheckIntro' = [labcheck].[introduction]
         ,'LabcheckUnit'  = [labcheck].[measurementunits]
		 ,[ChartIndex]
		 ,[AxisIndex]
   from 
         [diseaselabcheck] left join [labcheck] 
   on   
         [diseaselabcheck].[labcheckid] = [labcheck].[id]
   where
         [diseaselabcheck].[diseaseid] = @diseaseid
         
GO


/*******************************************************************
GetDiseaseStatRegion
*******************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetDiseaseStatRegion') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetDiseaseStatRegion
GO

create procedure GetDiseaseStatRegion
  @diseaseId int,
  @regionId int
as

if @regionId = 0
begin

    select 
		count([userinfo].[id]) as PatientCount
		, [userinfo].[provinceid] as RegionId
		, dbo.GetDistrictName([userinfo].[provinceid]) as RegionName
    from
		[userinfo],[patientrecord],[district]
    where 
		[userinfo].[id] = [patientrecord].[patientid]
    and
		[patientrecord].[diseaseid]=@diseaseId
    and 
		[userinfo].[provinceid] = [district].[id]
	and
		[district].[parentid] = 0 
    group by
		[userinfo].[provinceid]

end

else

begin
    select 
		count([userinfo].[id]) as PatientCount
		,[userinfo].[cityid] as RegionId
		, dbo.GetDistrictName([userinfo].[cityid]) as RegionName
    from
		[userinfo],[patientrecord],[district]
    where 
		[userinfo].[id] = [patientrecord].[patientid]
    and
		[patientrecord].[diseaseid]=@diseaseId
    and 
		[userinfo].[cityid] = [district].[id]
	and
		[userinfo].[cityid] = @regionId
    group by
		[userinfo].[cityid]
end   
   
GO


--region [dbo].[GetDiseaseStatAge]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetDiseaseStatAge]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetDiseaseStatAge]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetDiseaseStatAge]
GO

CREATE PROCEDURE [dbo].[GetDiseaseStatAge]
	@DiseaseId int,
	@StartAge int,
	@EndAge	int
AS
   
DECLARE @StrSQL VARCHAR(600)

SET @StrSQL = '
SELECT 
	COUNT([UserInfo].[id]) as PatientCount
FROM	
	[UserInfo], [PatientRecord]
WHERE 
	[UserInfo].[id] = [PatientRecord].[PatientId] AND
	[patientrecord].[DiseaseId] = ' + STR(@DiseaseId) + ' AND
	DATEDIFF(year, [PatientRecord].[StartDate], getdate()) > ' + STR(@StartAge) + ' 
'

IF @EndAge > 0
BEGIN	
	SET @StrSQL = @StrSQL + ' 
		AND DATEDIFF(year, [PatientRecord].[StartDate], getdate()) <= ' + STR(@EndAge) + '  
	'
END

EXEC(@StrSQL)

GO
--endregion


--region [dbo].[GetDiseaseStatDuration]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetDiseaseStatDuration]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetDiseaseStatDuration]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetDiseaseStatDuration]
GO

CREATE PROCEDURE [dbo].[GetDiseaseStatDuration]
	@DiseaseId int,
	@StartPoint int,
	@EndPoint	int
AS
   
DECLARE @StrSQL VARCHAR(600)

SET @StrSQL = '
SELECT 
	COUNT([UserInfo].[id]) as PatientCount
FROM	
	[UserInfo], [PatientRecord]
WHERE 
	[UserInfo].[id] = [PatientRecord].[PatientId] AND
	[patientrecord].[DiseaseId] = ' + STR(@DiseaseId) + ' AND
	DATEDIFF(year, [PatientRecord].[StartDate], getdate()) > ' + STR(@StartPoint) + ' 
'

IF @EndPoint > 0
BEGIN	
	SET @StrSQL = @StrSQL + ' 
		AND DATEDIFF(year, [PatientRecord].[StartDate], getdate()) <= ' + STR(@EndPoint) + '  
	'
END

EXEC(@StrSQL)

GO
--endregion


--region [GetDiseaseStatSymptom]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [GetDiseaseStatSymptom]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[GetDiseaseStatSymptom]') IS NOT NULL
	DROP PROCEDURE [GetDiseaseStatSymptom]
GO

CREATE PROCEDURE [GetDiseaseStatSymptom]
	@DiseaseId int
AS

SELECT 
	'PatientCount' = COUNT([PatientCurrentSymptom].[Id]), 
	'SymptomId' = [PatientCurrentSymptom].[SymptomId],
	'SymptomName' = dbo.GetSymptomName([PatientCurrentSymptom].[SymptomId]) 	
FROM	
	[PatientRecord], [PatientCurrentSymptom]
WHERE 
	[patientrecord].[DiseaseId] = @DiseaseId AND
	[PatientCurrentSymptom].[PRId] = [patientrecord].[Id] AND
	[PatientCurrentSymptom].[DegreeValue] > 0
Group by
	[PatientCurrentSymptom].[SymptomId]
ORDER BY
	PatientCount desc
GO
--endregion


--region [GetDiseaseStatTreatment]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [GetDiseaseStatTreatment]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[GetDiseaseStatTreatment]') IS NOT NULL
	DROP PROCEDURE [GetDiseaseStatTreatment]
GO

CREATE PROCEDURE [GetDiseaseStatTreatment]
	@DiseaseId int
AS

SELECT 
	'PatientCount' = COUNT([PatientCurrentTreatment].[Id]), 
	'TreatmentId' = [PatientCurrentTreatment].[TreatmentId],
	'TreatmentName' =  dbo.GetTreatmentName([PatientCurrentTreatment].[TreatmentId]) 	
FROM	
	[PatientRecord], [PatientCurrentTreatment]
WHERE 
	[patientrecord].[DiseaseId] = @DiseaseId AND
	[PatientCurrentTreatment].[PRId] = [PatientRecord].[Id] AND
	[PatientCurrentTreatment].[IsUsing] > 0
Group by
	[PatientCurrentTreatment].[TreatmentId]
ORDER BY
	PatientCount desc

GO
--endregion


--region [dbo].[GetDiseaseTreatmentListByTreatmentId]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetDiseaseTreatmentListByTreatmentId]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetDiseaseTreatmentListByTreatmentId]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetDiseaseTreatmentListByTreatmentId]
GO

CREATE procedure [dbo].[GetDiseaseTreatmentListByTreatmentId]
 	@TreatmentId int,
	@count int 
 AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
 SELECT Top ' + STR(@count)+
         ' ''Id'' = [disease].[Id],''Name'' = [disease].[diseasename]
   
 from 
         [diseasetreatment] left join [disease] 
 on   
         [diseasetreatment].[diseaseid] = [disease].[id]
 where
         [diseasetreatment].[treatmentid] = '+STR(@TreatmentId)+'
 Order by [disease].[PatientCount] desc'

EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[GetDiseaseListBySymptomId]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetDiseaseListBySymptomId]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetDiseaseListBySymptomId]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetDiseaseListBySymptomId]
GO

CREATE procedure [dbo].[GetDiseaseListBySymptomId]
 	@SymptomId int,
	@count int 
 AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL nvarchar(3250)

SET @SQL = '
 SELECT Top ' + STR(@count)+
         ' ''Id'' = [disease].[Id],''Name'' = [disease].[diseasename]
   
 from 
         [diseasesymptom] left join [disease] 
 on   
         [diseasesymptom].[diseaseid] = [disease].[id]
 where
         [diseasesymptom].[symptomid] = '+STR(@SymptomId)+'
 Order by [disease].[PatientCount] desc'

EXEC sp_executesql @SQL

GO
--endregion